Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


Oracle SNMP Agents

Oracle SNMP Agents are available in Oracle version 7.3 and later. The Oracle SNMP agents allow third-party vendors to easily retrieve performance data from Oracle. These SNMP agents in conjunction with OS SNMP agents can provide a complete management solution for your system. Oracle has teamed up with other members of the System Management Tools Initiative (SMTI) to provide a shrink-wrapped solution to your RDBMS management needs.

When you use a performance monitoring tool that uses the Oracle SNMP agents, you can avoid selecting information from the V$ tables directly. When you access the V$ tables, you cause some overhead within the database itself. When you use the Oracle SNMP agents, this information is retrieved in a more efficient manner (because Oracle designed it specifically for the RDBMS).

The Oracle SNMP agents are designed not only to give you reliable and pertinent information, but to cause as little overhead on the system as possible. Most SMTI and other third-party vendors will soon begin collecting their information through the Oracle SNMP agents (if they have not already done so).

SQL Trace

Oracle’s SQL Trace facility provides performance information about individual SQL statements. SQL Trace is activated by executing the following command:

SQL> ALTER SESSION SET SQL_TRACE = TRUE;

SQL Trace is turned off with this command:

SQL> ALTER SESSION SET SQL_TRACE = FALSE;

During the time that SQL Trace is active, performance statistics for all SQL statements generated within this session are stored in a trace file. You can then use the Oracle utility TKPROF to convert the trace file into readable form. TKPROF can also be invoked with the EXPLAIN PLAN option if you want to additionally display the execution plan. The following statistics are gathered for each SQL statement:

  Parse, execute and fetch counts.
  CPU and elapsed times.
  Physical reads and logical writes. Remember that the DBWR process does the writes in a deferred manner.
  Number of rows processed.
  Library cache misses.

SQL Trace can be enabled for all users by setting the initialization parameter SQL_TRACE to TRUE. The information gathered by SQL Trace can be quite useful in debugging inefficient SQL statements, as you will see in detail in Chapter 25, “Using EXPLAIN PLAN and SQL Trace.”

EXPLAIN PLAN

The Oracle EXPLAIN PLAN command displays the execution plan chosen by the Oracle optimizer for SELECT, UPDATE, INSERT, and DELETE statements. By examining the execution plan, you can see exactly how Oracle executes your SQL statement. The execution plan can help you determine whether you have written the an efficient SQL statement or whether changes can be made to optimize the statement.

To execute an EXPLAIN PLAN statement, you must first create a table with the name plan_table and with the specified plan_table format. The table format and the SQL statement required to create the table are included in Oracle in the SQL script UTXPLAN.SQL. Once this table is created, you can execute the EXPLAIN PLAN statement by issuing these SQL statements followed by your SQL statements:

EXPLAIN PLAN
SET STATEMENT_ID = 'NAME'
FOR

In this syntax, NAME specifies a label for the statement in the plan_table table. Whether you execute it on its own or in conjunction with the SQL Trace facility, the EXPLAIN PLAN command provides useful information about how SQL statements can be optimized.

OS Tools

Every operating system has a set of tools to help you monitor the system. Although the tools themselves differ depending on whether you are running UNIX, NetWare, NetWare NT, OS/2, or some other OS, each tool gives the same basic information to the administrator. The values that the OS monitors should include the following basic parameters:

  CPU utilization. In an Symmetric Multiprocessor (SMP) environment, this information should be broken down by CPU.
  Disk I/O. This information should be reported on a per-disk basis (or on a per-volume basis if you are using a disk array).
  Memory utilization. This statistic should indicate the amount of physical memory used and available.
  Paging statistics. If you are using a virtual memory operating system, this statistic should be available.
  Context or process switches. For any multitasking OS, this switch information is important.

These are but a few of the important indicators you will require in the upcoming chapters. Although each operating system is different and has different monitoring tools, the basic concepts are the same.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.